# 第一题
# def count_char_freq(input_str):
#     char_freq = {}
#     for char in input_str:
#         if char in char_freq:
#             char_freq[char] += 1
#         else:
#             char_freq[char] = 1
#     return char_freq
#
#
# if __name__ == "__main__":
#     input_str = input()
#     freq = count_char_freq(input_str)
#     print(freq)
# 第二题
# def is_prime(num):
#     if num < 2:
#         return False
#     for i in range(2, int(num ** 0.5) + 1):
#         if num % i == 0:
#             return False
#     return True
#
#
# if __name__ == "__main__":
#     prime_numbers = []
#     for num in range(1001, 2001):
#         if is_prime(num):
#             prime_numbers.append(num)
#
#     print(prime_numbers)
#     print(f"共有素数{len(prime_numbers)}个")
# 第三和第四题
# class Animal:
#     def __init__(self, name, age, sex, legNum, weight):
#         self.name = name
#         self.__age = age
#         self.__sex = sex
#         self.__legNum = legNum
#         self.__weight = weight
#
#     def eating(self, food):
#         print("Animal eating")
#
#     def __str__(self):
#         return f"Name: {self.name}, Age: {self.__age}, Sex: {self.__sex}, Leg Number: {self.__legNum}, Weight: {self.__weight}"
#
#
# class Chicken(Animal):
#     def __init__(self, name, age, sex, legNum, weight, combColor):
#         super().__init__(name, age, sex, legNum, weight)
#         self.combColor = combColor
#
#     def eating(self, food):
#         print(f"{self.name} eating {food}")
#
#     def flying(self):
#         print(f"{self.name} flying...")
#
#     def __str__(self):
#         return super().__str__() + f", Comb Color: {self.combColor}"
#
#
# if __name__ == "__main__":
#     chicken = Chicken("Chicken1", 2, "female", 2, 3, "red")
#     print(chicken)
#     chicken.eating("seeds")
#     chicken.flying()
# 第五题
# import pandas as pd
#
# df = pd.read_excel('2022届毕业设计第一次答辩分组.xlsx')
# fields = df.iloc[0]
# df = df.ffill(axis=0)
# for column in df.columns:
#     df[column] = df[column].where(df[column] != fields[column], None)
# df.iloc[0] = fields
# df.columns = ["", "                       软件工程专业2022届本科生毕业设计答辩分组明细", "", "", "", "", "", "", ""]
# df.to_excel('处理后的文件.xlsx', index=False)
# 第六题
# import pandas as pd
#
# df = pd.read_excel('处理后的文件.xlsx', header=1)
# grouped = df.groupby('组别')
# for name, group in grouped:
#     filename = f'{name}.xlsx'
#     group.to_excel(filename, index=False)
# 第7题
# import requests
# import re
#
# url = r'https://search.bilibili.com/all?keyword=python'
# headers = {
#     'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36'
# }
# resp = requests.get(url, headers=headers)
# page_content = resp.content.decode("utf-8")
# pat_str = r'''<a href="(?P<videoURL>.*?)".*?'''  # videoURL
# pat_str = pat_str + r'''<picture class=.*?><img src="(?P<imgURL>.*?)@.*?alt="(?P<videoTitle>.*?)".*?>.*?</picture>'''  # videoTitle imgURL
# pattern = re.compile(pat_str, re.S)
# result = pattern.finditer(page_content)
# num = 1
# for it in result:
#     dic = it.groupdict()
#     print(f"{num}:title = {dic['videoTitle']}, img = https:{dic['imgURL']}, url = https:{dic['videoURL']}")
#     num += 1
#第八题
from pymysql import connect
import requests
import re
conn = connect(host='localhost', port=3306, user='root', password='1113', database='finaltest', charset='utf8')
cs = conn.cursor()
url = r'https://search.bilibili.com/all?keyword=python'
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36'
}
resp = requests.get(url, headers=headers)
page_content = resp.content.decode("utf-8")
pat_str = r'''<a href="(?P<videoURL>.*?)".*?'''  # videoURL
pat_str = pat_str + r'''<picture class=.*?><img src="(?P<imgURL>.*?)@.*?alt="(?P<videoTitle>.*?)".*?>.*?</picture>'''  # videoTitle imgURL
pattern = re.compile(pat_str, re.S)
result = pattern.finditer(page_content)
num = 1
for it in result:
    dic = it.groupdict()
    tem_id = num
    tem_videoTitle = dic['videoTitle']
    tem_imgURL = dic['imgURL']
    tem_videoURL = dic['videoURL']
    try:
        sql = "insert into pythonVideo(id, videoTitle, imgURL, videoURL) values ('%d', '%s', '%s', '%s');" % (
        tem_id, tem_videoTitle, tem_imgURL, tem_videoURL)
        row_count = cs.execute(sql)
        conn.commit()
    except Exception as e:
        print("有问题！！！")
        conn.rollback()
    num += 1

sql = "select * from pythonVideo;"
row_count = cs.execute(sql)
print(f"SQL 语句执行影响的行数{row_count}")
for line in cs.fetchall():
    print(line)

cs.close()
conn.close()